package com.hngm.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.hngm.bean.Product;
import com.hngm.bean.User;
import com.hngm.util.DBManager;
import com.hngm.util.QueryDataResult;
import com.hngm.util.StringUtil;

/**
 * 用户表的数据访问层
 * */
public class UserDao {
    //根据用户名和密码查询用户信息的方法
	public User queryUserByUsernameAndPwd(String username ,String pwd){
		User user = null;
		String sql = String.format("select * from usertable where username = '%s' and pwd = '%s'", username,pwd);
		
		QueryDataResult qdr = DBManager.executeQuery(sql);
		
		ResultSet rs = qdr.getRs();
		
		try {
			while(rs.next()){
				String u = rs.getString("username");
				String p = rs.getString("pwd");
				long id = rs.getLong("id");
				
				user = new User();
				user.setId(id);
				user.setUsername(username);
				user.setPwd(pwd);
				
				return user;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		
		return user;
	}

	public ArrayList<User> queryuserListByParm(String username, String orderby, int beginIndex, int limit) {
		        ArrayList<User> usList = new ArrayList<User>();
				StringBuffer sb = new StringBuffer("select * from usertable where 1=1");
				// 拼接查询条件
				if (StringUtil.isNotNullorEmpty(username)) {
					// sb.append(String.format(" and name like '%s%s%s'",
					// "%",productname,"%"));
					sb.append(String.format(" and username like '%s'", "%" + username + "%"));
				}
				if (StringUtil.isNotNullorEmpty(orderby)) {
					if ("createtimeasc".equals(orderby)) {
						sb.append(" order by createtime asc ");
					} else if ("createtimedesc".equals(orderby)) {
						sb.append(" order by createtime desc ");
					}
				}
				sb.append(String.format(" limit %d,%d ", beginIndex, limit));
				QueryDataResult qdr = DBManager.executeQuery(sb.toString());
				
				ResultSet rs = qdr.getRs();
				
				try {
					while(rs.next()){
						long id = rs.getLong("id");
						String username1 = rs.getString("username");
						String pwd = rs.getString("pwd");
						String createtime = rs.getString("createtime");
						User us = new User(id, username1, pwd, createtime);
						usList.add(us);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
				}
				
				return usList;
			
	}

	public int queyruserListByParamCount(String username, String orderby) {
		StringBuffer sb = new StringBuffer("select count(1) as count from usertable where 1=1");

		// 拼接查询条件
		if (StringUtil.isNotNullorEmpty(username)) {
			// sb.append(String.format(" and name like '%s%s%s'",
			// "%",productname,"%"));
			sb.append(String.format(" and username like '%s'", "%" + username + "%"));
		}
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sb.toString());
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				return rs.getInt("count");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return 0;
	}

	public int insertUser(User p) {
		String sql = String.format("insert into usertable (username,pwd) values ('%s','%s')", p.getUsername(),p.getPwd());
		return DBManager.executeUpdate(sql);
	}

	public static int delProduct(String userId) {
		//定义删除产品的sql语句
		String sql = "delete from usertable where id = " + userId;
		return DBManager.executeUpdate(sql);
	}

	public int editUser(User p) {
		String sql = String.format("update usertable set username='%s',pwd='%s' where id = %d", p.getUsername(),p.getPwd(),p.getId());
		return DBManager.executeUpdate(sql);
	}
	
}
